﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CuaHangLaptop.BIZ;

namespace CuaHangLaptop.DAL
{
    class LaptopIMG_DAL
    {
        // CHUỖI KẾT NỐI
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; 
                                                 AttachDbFilename=C:\Users\Long Khong\Documents\Visual Studio 2010\Projects\CuaHangLaptop\CuaHangLaptop\Database\CuaHangLapTop.mdf;
                                                 Integrated Security=True; Connect Timeout=30; User Instance=True");

        public int Get_Lasted_ID()
        {
            int id = 0;
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select id from HinhAnhLapTop order by id desc", conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                id = rdr.GetInt32(0);
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return id;
        }

        // SELECT * FROM NHA SAN XUAT
        public List<LaptopIMG_BIZ> GetAll_IMG()
        {
            List<LaptopIMG_BIZ> result = new List<LaptopIMG_BIZ>();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select * from HinhAnhLapTop", conn);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    LaptopIMG_BIZ dd = new LaptopIMG_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3));
                    result.Add(dd);
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return result;
        }

        // LẤY NHÀ SẢN XUẤT CÓ MANSX = NSX.MANSX
        public LaptopIMG_BIZ Get_IMG_ByCode(string _maIMG)
        {
            LaptopIMG_BIZ _img;
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select * from HinhAnhLapTop where MaHinhAnh='{0}'", _maIMG);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                _img = new LaptopIMG_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3));
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return _img;
        }

        // LẤY TẤT CẢ NHÀ SẢN XUẤT CÓ MANSX = NSX.MANSX
        public List<LaptopIMG_BIZ> GetAll_IMG_ByLaptop(string _maLaptop)
        {
            List<LaptopIMG_BIZ> result = new List<LaptopIMG_BIZ>();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select * from HinhAnhLapTop where Malaptop='{0}'", _maLaptop);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    LaptopIMG_BIZ dd = new LaptopIMG_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3));
                    result.Add(dd);
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return result;
        }

        // DELETE
        internal void Delete(LaptopIMG_BIZ img)
        {
            try
            {
                conn.Open();
                String s = String.Format("delete from HinhAnhLapTop where id={0}", img.ID);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                conn.Close();
            }
        }

        // UPDATE
        internal void Update(LaptopIMG_BIZ img)
        {
            try
            {
                conn.Open();
                String s = String.Format("update HinhAnhLapTop set MaHinhAnh='{0}', maLaptop='{1}', url='{2}' where id={3}", img.MaIMG, img.Laptop.MaLaptop, img.URL, img.ID);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                conn.Close();
            }
        }

        // INSERT
        internal int Insert(LaptopIMG_BIZ img)
        {
            int newId = -1;
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("insert into HinhAnhLapTop values('{0}', '{1}', '{2}')", img.MaIMG, img.Laptop.MaLaptop, img.URL);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                    s = String.Format("select Id from HinhAnhLapTop where MaHinhAnh='{0}'", img.MaIMG);
                    cmd.CommandText = s;
                    rdr = cmd.ExecuteReader();

                    if (rdr.HasRows)
                    {
                        rdr.Read();
                        newId = Int32.Parse(rdr["Id"].ToString());
                    }
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return newId;
        }
    }
}
